﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using OnlineCooperation.Entity;

namespace OnlineCooperation.DB
{
    public class PostDB
    {
        String strConn;
        datasetMgn DSMgn;
        public Team team;                                                                                        //该PostDB记录的是团队team的新鲜事（含回复）
        //该post所属团队的所有新鲜事的列表（含回复），内部按照PostID排序，相同PostID则按照StateID排序
        public List<Post> AllPost;
        public int posts_per_page;                                                                           //每页加载新鲜事条数
        public int CurrentBegin;                                                                             //当前页的第一条新鲜事为AllPost[CurrentBegin]
        public int CurrentEnd;                                                                                //当前页的最后一条新鲜事为AllPost[CurrentEnd]

        //构造函数
        public PostDB(Team team, int ppp)
        {
            strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DSMgn = new datasetMgn();
            this.team = team;
            AllPost = new List<Post>();
            posts_per_page = ppp;
            CurrentBegin = 0;
            CurrentEnd = 0;
            //获取该团队所有新鲜事及其回复
            String strSql = "SELECT * FROM `post` WHERE `PostTeamID`='" + team.ID + "'ORDER BY `PostID`, `StateID`";
            DataSet ds = MySqlHelper.ExecuteDataset(strConn, strSql);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                int postid = Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString());
                int stateid = Convert.ToInt32(ds.Tables[0].Rows[i][1].ToString());
                String content = ds.Tables[0].Rows[i][2].ToString();
                int topicid = Convert.ToInt32(ds.Tables[0].Rows[i][3].ToString());
                int teamid = Convert.ToInt32(ds.Tables[0].Rows[i][4].ToString());
                DateTime dateandtime = Convert.ToDateTime(ds.Tables[0].Rows[i][5].ToString());
                Post TempPost = new Post(postid, stateid, content, topicid, teamid, dateandtime);
                AllPost.Add(TempPost);
            }
        }

        /// <summary>
        /// 加载下一页的所有新鲜事
        /// </summary>
        /// <returns>查询结果：返回下一页所有新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetNextPagePost()
        {
            // 若当前已是最后一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentEnd == AllPost.Count - 1)
                return null;
            
            List<Post> NextPagePost = new List<Post>();
            int p = CurrentBegin = CurrentEnd + 1;
            //下一页第一条新鲜事的PostID
            int BeginPostID = AllPost[CurrentBegin].PostID;
            int PostNum = 0;
            for (int i = BeginPostID; PostNum < posts_per_page && p < AllPost.Count; i++)
            {
                if (AllPost[p].PostID != i)
                    continue;
                while (p < AllPost.Count && AllPost[p].PostID == i)
                {
                    NextPagePost.Add(AllPost[p]);
                    p++;
                }
                PostNum++;
            }
            CurrentEnd = p - 1;
            return NextPagePost;
        }

        /// <summary>
        /// 加载上一页的所有新鲜事
        /// </summary>
        /// <returns>查询结果：返回上一页所有新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetPrePagePost()
        {
            // 若当前已是第一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentBegin == 0)
                return null;
            
            List<Post> PrePagePost = new List<Post>();
            int p = CurrentEnd = CurrentBegin - 1;
            //上一页最后一条新鲜事的PostID
            int EndPostID = AllPost[CurrentEnd].PostID;
            int PostNum = 0;
            for (int i = EndPostID; PostNum < posts_per_page && p >= 0; i--)
            {
                if (AllPost[p].PostID != i)
                    continue;
                while (p >= 0 && AllPost[p].PostID == i)
                {
                    PrePagePost.Insert(0, AllPost[p]);
                    p--;
                }
                PostNum++;
            }
            CurrentBegin = p + 1;
            return PrePagePost;
        }

        /// <summary>
        /// 加载第一页的所有新鲜事
        /// </summary>
        /// <returns>查询结果：返回第一页所有新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetFirstPagePost()
        {
            // 若当前已是第一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentBegin == 0 && CurrentEnd > 0)
                return null;
            
            List<Post> FirstPagePost = new List<Post>();
            int p = CurrentBegin = 0;
            int PostNum = 0;
            for (int i = 0; PostNum < posts_per_page && p < AllPost.Count; i++)
            {
                if (AllPost[p].PostID != i)
                    continue;
                while (p < AllPost.Count && AllPost[p].PostID == i)
                {
                    FirstPagePost.Add(AllPost[p]);
                    p++;
                }
                PostNum++;
            }
            CurrentEnd = p - 1;
            return FirstPagePost;
        }

        /// <summary>
        /// 加载最后一页的所有新鲜事，条数<=posts_per_page
        /// </summary>
        /// <returns>查询结果：返回最后一页所有新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetLastPagePost()
        {
            // 若当前已是最后一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentEnd == AllPost.Count - 1)
                return null;

            int p = CurrentEnd = AllPost.Count - 1;
            int total = 1;
            for (int i = 1; i < AllPost.Count; i++)
            {
                if (AllPost[i].PostID != AllPost[i - 1].PostID)
                    total++;
            }
            //最后一页加载新鲜事条数
            int LastPagePostNum = ((total % posts_per_page) == 0) ? posts_per_page : (total % posts_per_page); 
            List<Post> LastPagePost = new List<Post>();

            int PostNum = 0;
            for (int i = AllPost[CurrentEnd].PostID; PostNum < LastPagePostNum && p >= 0; i--)
            {
                if (AllPost[p].PostID != i)
                    continue;
                while (p >= 0 && AllPost[p].PostID == i)
                {
                    LastPagePost.Insert(0,AllPost[p]);
                    p--;
                }
                PostNum++;
            }
            CurrentBegin = p + 1;
            return LastPagePost;
        }

        /// <summary>
        /// 加载下一页的所有特定话题新鲜事
        /// </summary>
        /// <returns>查询结果：返回下一页所有特定话题新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetNextPagePost(Topic topic)
        {
            // 若当前已是最后一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentEnd == AllPost.Count - 1)
                return null;

            //寻找Allpost列表CurrentEnd之后关联该话题的新鲜事中PostID最小的一个，记为AllPost[phead]
            int phead = CurrentEnd + 1;
            while (phead < AllPost.Count && !(AllPost[phead].StateID == 0 && AllPost[phead].TopicID == topic.ID))
                phead++;
            if (phead == AllPost.Count)
                return null;
            CurrentBegin = CurrentEnd + 1;

            //在AllPost[phead]之后查询
            List<Post> NextPagePost = new List<Post>();
            int PostNum = 0;
            while (PostNum < posts_per_page)
            {
                while (phead < AllPost.Count && !(AllPost[phead].StateID == 0 && AllPost[phead].TopicID == topic.ID))
                    phead++;
                if (phead == AllPost.Count)
                {
                    CurrentEnd = AllPost.Count - 1;
                    return NextPagePost;
                }

                //加载满足条件的新鲜事及其回复
                int CurrentPostID = AllPost[phead].PostID;
                while (phead < AllPost.Count && AllPost[phead].PostID == CurrentPostID)
                {
                    NextPagePost.Add(AllPost[phead]);
                    phead++;
                    if (phead == AllPost.Count)
                    {
                        CurrentEnd = AllPost.Count - 1;
                        return NextPagePost;
                    }
                }
                PostNum++;
            }
            CurrentEnd = phead - 1;
            return NextPagePost;
        }

        /// <summary>
        /// 加载上一页的所有特定话题新鲜事
        /// </summary>
        /// <returns>查询结果：返回上一页所有特定话题新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetPrePagePost(Topic topic)
        {
            // 若当前已是第一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentBegin == 0)
                return null;

            //寻找Allpost列表CurrentBegin之前关联该话题的新鲜事中PostID最大的一个，记为AllPost[ptail]
            int ptail = CurrentBegin - 1;
            while (ptail >= 0 && !(AllPost[ptail].StateID == 0 && AllPost[ptail].TopicID == topic.ID))
                ptail--;
            if (ptail == -1)
                return null;
            CurrentEnd = CurrentBegin - 1;

            //在AllPost[ptail]之前查询
            List<Post> PrePagePost = new List<Post>();
            int PostNum = 0;
            while (PostNum < posts_per_page)
            {
                while (ptail >= 0 && !(AllPost[ptail].StateID == 0 && AllPost[ptail].TopicID == topic.ID))
                    ptail--;
                if (ptail == -1)
                {
                    CurrentBegin = 0;
                    return PrePagePost;
                }

                //加载满足条件的新鲜事
                PrePagePost.Insert(0, AllPost[ptail]);
                //加载其回复
                int match = ptail + 1, p = 1;
                int CurrentPostID = AllPost[ptail].PostID;
                while (match < AllPost.Count && AllPost[match].PostID == CurrentPostID)
                {
                    PrePagePost.Insert(p, AllPost[match]);
                    match++;
                    p++;
                }
                PostNum++;
                ptail--;
            }
            CurrentBegin = ptail + 1;
            return PrePagePost;
        }

        /// <summary>
        /// 加载第一页的所有特定话题新鲜事
        /// </summary>
        /// <returns>查询结果：返回第一页所有特定话题新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetFirstPagePost(Topic topic)
        {
            // 若当前已是第一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentBegin == 0 && CurrentEnd > 0)
                return null;

            int p = 0;
            while (p < AllPost.Count && !(AllPost[p].StateID == 0 && AllPost[p].TopicID == topic.ID))
                p++;
            if (p == AllPost.Count)
                return null;

            //从AllPost[p]（Allpost列表中关联该话题的新鲜事中PostID最小的一个）开始查询
            List<Post> FirstPagePost = new List<Post>();
            CurrentBegin = 0;
            int PostNum = 0;
            while (PostNum < posts_per_page)
            {
                while (p < AllPost.Count && !(AllPost[p].StateID == 0 && AllPost[p].TopicID == topic.ID))
                    p++;
                if (p == AllPost.Count)
                {
                    CurrentEnd = AllPost.Count - 1;
                    return FirstPagePost;
                }

                int CurrentPostID = AllPost[p].PostID;
                while (p < AllPost.Count && AllPost[p].PostID == CurrentPostID)
                {
                    FirstPagePost.Add(AllPost[p]);
                    p++;
                    if (p == AllPost.Count)
                    {
                        CurrentEnd = AllPost.Count - 1;
                        return FirstPagePost;
                    }
                }
                PostNum++;
            }
            CurrentEnd = p - 1;
            return FirstPagePost;
        }

        /// <summary>
        /// 加载最后一页的所有特定话题新鲜事，条数<=posts_per_page
        /// </summary>
        /// <returns>查询结果：返回最后一页所有特定话题新鲜事，更新CurrentBegin和CurrentEnd的值</returns>
        public List<Post> GetLastPagePost(Topic topic)
        {
            // 若当前已是最后一页，则返回null，停留在本页，CurrentBegin和CurrentEnd的值不变
            if (CurrentEnd == AllPost.Count - 1)
                return null;

            //计算最后一页加载的新鲜事数目total
            int total = 0;
            foreach (Post item in AllPost)
            {
                if (item.StateID == 0 && item.TopicID == topic.ID)
                    total++;
            }
            total = ((total % posts_per_page) == 0) ? posts_per_page : (total % posts_per_page);

            //寻找Allpost列表关联该话题的新鲜事中PostID最大的一个，记为AllPost[p]
            int p = AllPost.Count - 1;
            while (p >= 0 && !(AllPost[p].StateID == 0 && AllPost[p].TopicID == topic.ID))
                p--;
            if (p == -1)
                return null;
            CurrentEnd = AllPost.Count - 1;

            //在AllPost[p]之前查询
            List<Post> LastPagePost = new List<Post>();
            int PostNum = 0;
            while (PostNum < total)
            {
                while (p >= 0 && !(AllPost[p].StateID == 0 && AllPost[p].TopicID == topic.ID))
                    p--;
                if (p == -1)
                {
                    CurrentBegin = 0;
                    return LastPagePost;
                }

                //加载满足条件的新鲜事
                LastPagePost.Insert(0, AllPost[p]);
                //加载其回复
                int match = p + 1, t = 1;
                int CurrentPostID = AllPost[p].PostID;
                while (match < AllPost.Count && AllPost[match].PostID == CurrentPostID)
                {
                    LastPagePost.Insert(t, AllPost[match]);
                    match++;
                    t++;
                }
                PostNum++;
                p--;
            }
            CurrentBegin = p + 1;
            return LastPagePost;
        }

        /// <summary>
        /// 手动向数据库中插入一条新鲜事或者回复记录
        /// </summary>
        /// <param name="post">新鲜事或者回复，其StateID字段若为0则为新鲜事，为其他任何值则为回复且此时StateID字段无效</param>
        /// 若不明确post的TopicID，则用给定除TopicID外其余参数的构造函数进行初始化
        /// <returns>插入的结果：返回1成功，返回0失败</returns>
        public int Insert(Post post)
        {
            String strSql;
            //插入新鲜事
            if (post.StateID == 0)
            {
                strSql = "SELECT max(`PostID`) FROM `post`";
                String CurrentMaxPostID = DSMgn.Readstr(strSql, 0, strConn);
                if (CurrentMaxPostID == "")
                    CurrentMaxPostID = "-1";

                strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
                MySqlParameter[] parameters = new MySqlParameter[6];
                parameters[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
                parameters[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
                parameters[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
                parameters[1].Value = 0;
                parameters[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
                parameters[2].Value = post.Content;
                parameters[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
                parameters[3].Value = post.TopicID;
                parameters[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
                parameters[4].Value = post.TeamID;
                parameters[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
                parameters[5].Value = DateTime.Now;
                MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);

                return 1;
            }

            //插入回复
            strSql = "SELECT max(`StateID`) FROM `post` WHERE `PostID`='" + post.PostID + "'";
            String CurrentMaxStateID = DSMgn.Readstr(strSql, 0, strConn);
            if (CurrentMaxStateID == "")
                CurrentMaxStateID = "-1";

            strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
            MySqlParameter[] parameters2 = new MySqlParameter[6];
            parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
            parameters2[0].Value = post.PostID;
            parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
            parameters2[1].Value = Convert.ToInt32(CurrentMaxStateID) + 1;
            parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
            parameters2[2].Value = post.Content;
            parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
            parameters2[3].Value = post.TopicID;
            parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
            parameters2[4].Value = post.TeamID;
            parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
            parameters2[5].Value = DateTime.Now;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);

            return 1;
        }

        //删除post
        //返回1成功，不存在该post时返回0失败
        //若为新鲜事则删除新鲜事及其回复，若为回复就只删除回复
        public int Delete(Post post)
        {
            String strSql;
            DataSet ds;
            //删除新鲜事
            if (post.StateID == 0)
            {
                strSql = "SELECT  * FROM `post` WHERE `PostID`='" + post.PostID + "'";
                ds = DSMgn.ReadDataSet(strSql, strConn);
                if (ds.Tables[0].Rows.Count == 0)
                    return 0;

                strSql = "DELETE  FROM `post` WHERE `PostID`='" + post.PostID + "'";
                MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
                return 1;
            }

            //删除回复
            strSql = "SELECT  * FROM `post` WHERE `PostID`='" + post.PostID + "'AND `StateID`='" + post.StateID + "'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return 0;

            strSql = "DELETE  FROM `post` WHERE `PostID`='" + post.PostID + "'AND `StateID`='" + post.StateID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            return 1;
        }
    }
}